package com.teradata.fbj.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.teradata.fbj.pojo.linkParams;
import com.teradata.fbj.util.GetConnection;
import com.teradata.fbj.util.freeJDBC;

@Repository
public class ExcelDataDaoImpl implements IExcelDataDao {

	@Autowired
	private GetConnection getcon = new GetConnection();
	private freeJDBC freeJ = new freeJDBC();
	private Connection connection = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	public void loadData(List<List<String>> excelData, linkParams lp, String tableName) {
		List<String> columnList = new ArrayList<String>();
		try {
			connection = getcon.getConnection(lp);
			if (connection == null) {
				System.out.println("连接失败！");
			}
			int result = 1;
			String sql = "";
			for (int i = 1; i < excelData.size(); i++) {
				columnList = excelData.get(i);
				sql = "insert into " + tableName + " values(";
				for (int j = 0; j < columnList.size(); j++) {
					sql += "?";
					if (j < columnList.size() - 1) {
						sql += ",";
					}
				}
				sql += ")";
				excutePs(sql, columnList);
				result = ps.executeUpdate();
			}
			// connection.commit();
		} catch (SQLException e) {
			System.out.println("添加数据失败" + e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} catch (Exception e1) {
			System.out.println("回滚失败：" + e1.getMessage());

		} finally {
			freeJ.free(rs, ps, connection);
		}

	}

	// 预处理SQL 防止注入 将参数传入sql中
	private void excutePs(String sql, List<String> columnList) throws SQLException {
		ps = connection.prepareStatement(sql);
		// 注入参数
		for (int i = 0; i < columnList.size(); i++) {
			ps.setString(i + 1, columnList.get(i));
		}
	}

}
